import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import java.applet.*;

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author jiwonhan, Justin Tang
 */
public class DBConnect extends Applet {

	private Connection conn;
	public PasswordEncryption pe;
	public DBConnect(){
		pe = new PasswordEncryption();

		try{
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://143.215.101.106:3306/quirklco_new","quirklco_jtang","qwerty1234");
			//CallableStatement stmt = conn.prepareCall("CALL quirklco_createUser");

		}   
		catch(Exception ex){
			System.out.println("Error: "+ex);

		}
	}
	
	public String getEncryptedPassword(String password){
		return pe.getSalt() + pe.md5(password) + pe.getSalt();
	}

	public boolean checkLoginCredentials(String userName, String password) throws SQLException{
		CallableStatement stmt = conn.prepareCall("CALL quirklco_getPasswordToCheck(?)");
		stmt.setString(1, userName);
		ResultSet result = stmt.executeQuery();
		String storedPassword = "";
		if(result.next()){
			storedPassword = result.getString("Password");
		}
		else{
			return false;
		}
		if(!storedPassword.substring(pe.getSaltLength(),storedPassword.length()-pe.getSaltLength()).equals(pe.md5(password))){
			return false;
		}
		return true;
	}
	/*
    createUserProc(conn, name, password)
    conn is for the connection for the database
    name is the input name that you want to add
    password is the password you want to create
    stmt.setString(1,name) is saying the 1st index is the name which in the table the 1st column is for name
    stmt.setString(2,password) is the password since 2nd is the password column
    stmt.executeQuery();
	 */
	public boolean createUserProc(String username, String password, String name) throws SQLException{
		CallableStatement checkStmt = conn.prepareCall("CALL quirklco_checkIfUserExists(?)");
		checkStmt.setString(1, username);
		ResultSet results = checkStmt.executeQuery();
		if(results.next()){
			return false;
		}
		CallableStatement stmt = conn.prepareCall("CALL quirklco_createUser(?,?,?)");
		stmt.setString(1, username);
		stmt.setString(2, getEncryptedPassword(password));
		stmt.setString(3, name);
		stmt.executeQuery();
		return true;
	}

	public void loginUserProc(String name, String password) throws SQLException{

		CallableStatement stmt = conn.prepareCall("CALL quirklco_loginUser(?,?)");
		stmt.setString(1, name);
		stmt.setString(2,password);
		stmt.executeQuery();
	}
	/*
     For this prcoedure in the resul.getString(1) means to retrieve a string in the first column and so forth 
	 */

	public JSONArray featuredArtProc() throws SQLException{
		JSONArray arr = new JSONArray();
		CallableStatement stmt = conn.prepareCall("CALL quirklco_featuredArt()");
		ResultSet result = stmt.executeQuery();
		int i = 0;
		while(result.next()){
			JSONObject obj = new JSONObject();
			//System.out.println(resul.getString("UserName")+"\t"+resul.getString(2)+"\t"+resul.getInt(3)+"\t"+resul.getInt(4)+"\t"+resul.getInt(5)+"\t"+resul.getString(6)+"\t"+resul.getString(7));
			obj.put("UserName", result.getString("UserName"));
			obj.put("Name", result.getString("Name"));
			obj.put("ArtID", result.getInt("ArtID"));
			obj.put("Ratings", result.getInt("Ratings"));
			obj.put("Views", result.getString("ThumbnailLocation"));
			obj.put("Description", result.getString("Description"));
			arr.add(obj);
		}
		stmt.close();
		return arr;

	}

	public JSONArray topArtProc() throws SQLException{
		JSONArray arr = new JSONArray();
		CallableStatement stmt = conn.prepareCall("CALL quirklco_topArt()");
		ResultSet result = stmt.executeQuery();
		while(result.next()){
			JSONObject obj = new JSONObject();
			//System.out.println(resul.getString(1)+"\t"+resul.getString(2)+"\t"+resul.getInt(3)+"\t"+resul.getInt(4)+"\t"+resul.getInt(5)+"\t"+resul.getString(6)+"\t"+resul.getString(7));
			obj.put("UserName", result.getString("UserName"));
			obj.put("Name", result.getString("Name"));
			obj.put("ArtID", result.getInt("ArtID"));
			obj.put("Ratings", result.getInt("Ratings"));
			obj.put("Views", result.getString("ThumbnailLocation"));
			obj.put("Description", result.getString("Description"));
			arr.add(obj);
		}
		stmt.close();
		return arr;
	}

	public JSONArray topFilmProc() throws SQLException{
		JSONArray arr = new JSONArray();
		CallableStatement stmt = conn.prepareCall("CALL quirklco_topFilm()");
		ResultSet result = stmt.executeQuery();
		while(result.next()){
			JSONObject obj = new JSONObject();
			//System.out.println(resul.getString(1)+"\t"+resul.getString(2)+"\t"+resul.getInt(3)+"\t"+resul.getInt(4)+"\t"+resul.getInt(5)+"\t"+resul.getString(6)+"\t"+resul.getString(7));
			obj.put("UserName", result.getString("UserName"));
			obj.put("Name", result.getString("Name"));
			obj.put("FilmID", result.getInt("FilmID"));
			obj.put("Ratings", result.getInt("Ratings"));
			obj.put("Views", result.getString("ThumbnailLocation"));
			obj.put("Description", result.getString("Description"));
			arr.add(obj);
		}
		stmt.close();
		return arr;
	}

	public JSONArray topMusicProc() throws SQLException{
		JSONArray arr = new JSONArray();
		CallableStatement stmt = conn.prepareCall("CALL quirklco_topMusic()");
		ResultSet result = stmt.executeQuery();
		while(result.next()){
			JSONObject obj = new JSONObject();
			//System.out.println(resul.getString(1)+"\t"+resul.getString(2)+"\t"+resul.getInt(3)+"\t"+resul.getInt(4)+"\t"+resul.getInt(5)+"\t"+resul.getString(6)+"\t"+resul.getString(7));
			obj.put("UserName", result.getString("UserName"));
			obj.put("Name", result.getString("Name"));
			obj.put("MusicID", result.getInt("MusicID"));
			obj.put("Ratings", result.getInt("Ratings"));
			obj.put("Views", result.getString("ThumbnailLocation"));
			obj.put("Description", result.getString("Description"));
			arr.add(obj);
		}
		stmt.close();
		return arr;
	}

	public JSONArray topSeriesProc() throws SQLException{
		JSONArray arr = new JSONArray();
		CallableStatement stmt = conn.prepareCall("CALL quirklco_topSeries()");
		ResultSet result = stmt.executeQuery();
		while(result.next()){
			JSONObject obj = new JSONObject();
			//System.out.println(resul.getString(1)+"\t"+resul.getString(2)+"\t"+resul.getInt(3)+"\t"+resul.getInt(4)+"\t"+resul.getInt(5)+"\t"+resul.getString(6)+"\t"+resul.getString(7));
			obj.put("UserName", result.getString("UserName"));
			obj.put("Name", result.getString("Name"));
			obj.put("SeriesID", result.getInt("SeriesID"));
			obj.put("Ratings", result.getInt("Ratings"));
			obj.put("Views", result.getString("ThumbnailLocation"));
			obj.put("Description", result.getString("Description"));
			arr.add(obj);
		}
		stmt.close();
		return arr;
	}  

	public JSONArray featuredFilmProc() throws SQLException{
		JSONArray arr = new JSONArray();
		CallableStatement stmt = conn.prepareCall("CALL quirklco_featuredFilm()");
		ResultSet result = stmt.executeQuery();
		while(result.next()){
			JSONObject obj = new JSONObject();
			//System.out.println(resul.getString(1)+"\t"+resul.getString(2)+"\t"+resul.getInt(3)+"\t"+resul.getInt(4)+"\t"+resul.getInt(5)+"\t"+resul.getString(6)+"\t"+resul.getString(7));
			obj.put("UserName", result.getString("UserName"));
			obj.put("Name", result.getString("Name"));
			obj.put("FilmID", result.getInt("FilmID"));
			obj.put("Ratings", result.getInt("Ratings"));
			obj.put("Views", result.getString("ThumbnailLocation"));
			obj.put("Description", result.getString("Description"));
			arr.add(obj);
		}
		stmt.close();
		return arr;
	}     
	public JSONArray featuredMusicProc() throws SQLException{
		JSONArray arr = new JSONArray();
		CallableStatement stmt = conn.prepareCall("CALL quirklco_featuredMusic()");
		ResultSet result = stmt.executeQuery();
		while(result.next()){
			JSONObject obj = new JSONObject();
			//System.out.println(resul.getString(1)+"\t"+resul.getString(2)+"\t"+resul.getInt(3)+"\t"+resul.getInt(4)+"\t"+resul.getInt(5)+"\t"+resul.getString(6)+"\t"+resul.getString(7));
			obj.put("UserName", result.getString("UserName"));
			obj.put("Name", result.getString("Name"));
			obj.put("MusicID", result.getInt("MusicID"));
			obj.put("Ratings", result.getInt("Ratings"));
			obj.put("Views", result.getString("ThumbnailLocation"));
			obj.put("Description", result.getString("Description"));
			arr.add(obj);
		}
		stmt.close();
		return arr;
	}

	public JSONArray featuredPerformancesProc() throws SQLException{
		JSONArray arr = new JSONArray();
		CallableStatement stmt = conn.prepareCall("CALL quirklco_featuredPerformances()");
		ResultSet result = stmt.executeQuery();
		while(result.next()){
			JSONObject obj = new JSONObject();
			//System.out.println(resul.getString(1)+"\t"+resul.getString(2)+"\t"+resul.getInt(3)+"\t"+resul.getInt(4)+"\t"+resul.getInt(5)+"\t"+resul.getString(6)+"\t"+resul.getString(7));
			obj.put("UserName", result.getString("UserName"));
			obj.put("Name", result.getString("Name"));
			obj.put("PerformanceID", result.getInt("PerformanceID"));
			obj.put("Ratings", result.getInt("Ratings"));
			obj.put("Views", result.getString("ThumbnailLocation"));
			obj.put("Description", result.getString("Description"));
			arr.add(obj);
		}
		stmt.close();
		return arr;
	} 

	public JSONArray featuredSeriesProc() throws SQLException{
		JSONArray arr = new JSONArray();
		CallableStatement stmt = conn.prepareCall("CALL quirklco_Series()");
		ResultSet result = stmt.executeQuery();
		while(result.next()){
			JSONObject obj = new JSONObject();
			//System.out.println(resul.getString(1)+"\t"+resul.getString(2)+"\t"+resul.getInt(3)+"\t"+resul.getInt(4)+"\t"+resul.getInt(5)+"\t"+resul.getString(6)+"\t"+resul.getString(7));
			obj.put("UserName", result.getString("UserName"));
			obj.put("Name", result.getString("Name"));
			obj.put("SeriesID", result.getInt("SeriesID"));
			obj.put("Ratings", result.getInt("Ratings"));
			obj.put("Views", result.getString("ThumbnailLocation"));
			obj.put("Description", result.getString("Description"));
			arr.add(obj);
		}
		stmt.close();
		return arr;
	}

	// We aren't using this method
	/*public void getAllFromUserIDProc(int id) throws SQLException{
        CallableStatement stmt = conn.prepareCall("CALL quirklco_Series(?)");
        stmt.setInt(1,id);
        ResultSet resul = stmt.executeQuery();
        while(resul.next()){
         System.out.println(resul.getString(1)+"\t"+resul.getString(2)+"\t"+resul.getInt(3)+"\t"+resul.getInt(4)+"\t"+resul.getInt(5)+"\t"+resul.getString(6));

        }
    }*/
	/*
     stmt.getString(2) is getting all the values from column 2 this might be a little different Justin has to check
	 */
	public JSONArray getAllMusicFromUserIDProc(int id) throws SQLException{
		JSONArray arr = new JSONArray();
		CallableStatement stmt = conn.prepareCall("CALL quirklco_getAllMusicFromUserID(?)");
		stmt.setInt(1,id);
		ResultSet result = stmt.executeQuery();
		//String musicFromUser = stmt.getString(2);
		while(result.next()){
			JSONObject obj = new JSONObject();
			obj.put("MusicID", result.getInt("MusicID"));
			obj.put("UserID", result.getInt("UserID"));
			obj.put("UserName", result.getString("UserName"));
			obj.put("Name", result.getString("Name"));
			obj.put("StatusFlag", result.getInt("StatusFlag"));
			obj.put("Location", result.getString("Location"));
			obj.put("FileSize", result.getInt("FileSize"));
			obj.put("Reported", result.getInt("Reported"));
			obj.put("Tags", result.getString("Tags"));
			obj.put("Ratings", result.getInt("Ratings"));
			obj.put("Views", result.getInt("Views"));
			obj.put("Email", result.getString("Email"));
			obj.put("CreateDate", result.getString("CreateDate"));
			obj.put("Featured", result.getInt("Featured"));
			obj.put("ThumbnailLocation", result.getString("ThumbnailLocation"));
			obj.put("Description", result.getString("Description"));
			arr.add(obj);
		}
		stmt.close();
		return arr;
	}

	public JSONArray getAllPerformancesFromUserIDProc(int id) throws SQLException{
		JSONArray arr = new JSONArray();
		CallableStatement stmt = conn.prepareCall("CALL quirklco_getAllPerformancesFromUserID(?)");
		stmt.setInt(1,id);
		ResultSet result = stmt.executeQuery();
		//String performanceFromUser = stmt.getString(2);
		while(result.next()){
			JSONObject obj = new JSONObject();
			obj.put("PerformanceID", result.getInt("PerformanceID"));
			obj.put("UserID", result.getInt("UserID"));
			obj.put("UserName", result.getString("UserName"));
			obj.put("Name", result.getString("Name"));
			obj.put("StatusFlag", result.getInt("StatusFlag"));
			obj.put("Location", result.getString("Location"));
			obj.put("FileSize", result.getInt("FileSize"));
			obj.put("Reported", result.getInt("Reported"));
			obj.put("Tags", result.getString("Tags"));
			obj.put("Ratings", result.getInt("Ratings"));
			obj.put("Views", result.getInt("Views"));
			obj.put("Email", result.getString("Email"));
			obj.put("CreateDate", result.getString("CreateDate"));
			obj.put("Featured", result.getInt("Featured"));
			obj.put("ThumbnailLocation", result.getString("ThumbnailLocation"));
			obj.put("Description", result.getString("Description"));
			arr.add(obj);
		}
		stmt.close();
		return arr;
	} 

	public JSONArray getAllFilmFromUserIDProc(int id) throws SQLException{
		JSONArray arr = new JSONArray();
		CallableStatement stmt = conn.prepareCall("CALL quirklco_getAllFilmFromUserID(?)");
		stmt.setInt(1,id);
		ResultSet result = stmt.executeQuery();
		//String filmsFromUser = stmt.getString(2);
		while(result.next()){
			JSONObject obj = new JSONObject();
			obj.put("FilmID", result.getInt("FilmID"));
			obj.put("UserID", result.getInt("UserID"));
			obj.put("UserName", result.getString("UserName"));
			obj.put("Name", result.getString("Name"));
			obj.put("StatusFlag", result.getInt("StatusFlag"));
			obj.put("Location", result.getString("Location"));
			obj.put("FileSize", result.getInt("FileSize"));
			obj.put("Reported", result.getInt("Reported"));
			obj.put("Tags", result.getString("Tags"));
			obj.put("Ratings", result.getInt("Ratings"));
			obj.put("Views", result.getInt("Views"));
			obj.put("Email", result.getString("Email"));
			obj.put("CreateDate", result.getString("CreateDate"));
			obj.put("Featured", result.getInt("Featured"));
			obj.put("ThumbnailLocation", result.getString("ThumbnailLocation"));
			obj.put("Description", result.getString("Description"));
			arr.add(obj);
		}
		stmt.close();
		return arr;
	}

	public JSONArray getAllSeriesFromUserIDProc(int id) throws SQLException{
		JSONArray arr = new JSONArray();
		CallableStatement stmt = conn.prepareCall("CALL quirklco_getAllSeriesFromUserID(?)");
		stmt.setInt(1,id);
		ResultSet result = stmt.executeQuery();
		//String seriesFromUser = stmt.getString(2);
		while(result.next()){
			JSONObject obj = new JSONObject();
			obj.put("FilmID", result.getInt("FilmID"));
			obj.put("UserID", result.getInt("UserID"));
			obj.put("UserName", result.getString("UserName"));
			obj.put("Name", result.getString("Name"));
			obj.put("StatusFlag", result.getInt("StatusFlag"));
			obj.put("Location", result.getString("Location"));
			obj.put("FileSize", result.getInt("FileSize"));
			obj.put("Reported", result.getInt("Reported"));
			obj.put("Tags", result.getString("Tags"));
			obj.put("Ratings", result.getInt("Ratings"));
			obj.put("Views", result.getInt("Views"));
			obj.put("Email", result.getString("Email"));
			obj.put("CreateDate", result.getString("CreateDate"));
			obj.put("Featured", result.getInt("Featured"));
			obj.put("ThumbnailLocation", result.getString("ThumbnailLocation"));
			obj.put("Description", result.getString("Description"));
			arr.add(obj);
		}
		stmt.close();
		return arr;
	} 
}
